package com.zk.hql.dao;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.jdbc.Work;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.orm.hibernate5.HibernateTemplate;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

/**
 * HibernateDAO
 *
 * @author Chang Fei
 */
@Repository
@SuppressWarnings({ "unchecked", "rawtypes" })
public class BaseHibernateDAO implements BaseDAO {

	@Autowired(required = true)
	private HibernateTemplate template;

	public HibernateTemplate getTemplate() {
		return template;
	}

	public <T> T findById(Class<?> clz, Serializable id) {
		return (T) template.get(clz, id);
	}

	public <T> T loadById(Class<?> clz, Serializable id) {
		T entity = (T) template.load(clz, id);
		return entity;
	}

	public <T> List<T> findAll(Class<?> clz) {
		List list = template.find("from " + clz.getName());
		return list;
	}

	public <T> T save(T entity) {
		try {
			template.save(entity);
			flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return entity;
	}

	public void update(Object entity) {
		template.saveOrUpdate(entity);
		flush();
	}

	public void update(Object entity, LockMode lock) {
		template.saveOrUpdate(entity);
		flush();
	}

	public <T> void saveOrUpdate(Object entities) {
		template.saveOrUpdate(entities);
		flush();
	}

	public <T> void saveOrUpdateAll(Collection<?> entities) {
		for (Object o : entities) {
			template.saveOrUpdate(o);
		}
		flush();
	}

	public void delete(Object entity) {
		template.delete(entity);
		flush();
	}

	public void delete(Object entity, LockMode lock) {
		template.delete(entity);
		flush();
	}

	public void deleteById(Class<?> clz, Serializable id) {
		this.delete(this.loadById(clz, id));
		flush();
	}

	public void deleteAll(Collection<?> entities) {
		getTemplate().deleteAll(entities);
		flush();
	}

	public void merge(Object entity) {
		template.merge(entity);
		flush();
	}

	public <T> T get(final String hsql) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<T>() {
			public T doInHibernate(Session session) throws HibernateException {
				return (T) session.createQuery(hsql).uniqueResult();
			}
		});
	}

	public <T> T get(Class<T> clazz, Serializable id) {
		return getTemplate().load(clazz, id);
	}

	public List get(String hql, Object... values) {
		return getTemplate().find(hql, values);
	}

	public <T> List<T> findList(String hsql) {
		List list = getTemplate().find(hsql);
		return (List<T>) list;
	}

	public List findList(final String hql, final int start, final int number, final Object... values) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				query.setFirstResult(start);
				query.setMaxResults(number);
				if (values != null && values.length > 0) {
					for (int i = 0; i < values.length; i++) {
						query.setParameter(i, values[i]);
					}
				}
				List list = query.list();
				return list;
			}
		});
	}

	public List findList(final String hql, final int start, final int number) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				query.setFirstResult(start);
				query.setMaxResults(number);
				List list = query.list();
				return list;
			}
		});
	}

	public List findList(String queryString, Object... values) {
		if (values != null && values.length > 0) {
			return getTemplate().find(queryString, values);
		}
		return getTemplate().find(queryString);
	}

	public List findByNamedQuery(String queryName) {

		return getTemplate().findByNamedQuery(queryName);
	}

	public <T> List<T> findByNamedQuery(String queryName, Object... values) {
		return (List<T>) getTemplate().findByNamedQuery(queryName, values);
	}

	public <T> List<T> findByNamedQuery(final String queryName, final String[] paramNames, final Object[] values) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				Query query = session.getNamedQuery(queryName);
				for (int i = 0; i < paramNames.length; i++) {
					query.setParameter(paramNames[i], values[i]);
				}
				return query.list();
			}
		});
	}

	public <T> Iterator<T> iterate(final String hql) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<Iterator>() {
			public Iterator doInHibernate(Session session) throws HibernateException {
				return session.createQuery(hql).iterate();
			}

		});

	}

	public <T> Iterator<T> iterate(final String hql, final Object... values) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<Iterator>() {
			public Iterator doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				for (int i = 0; i < values.length; i++) {
					query.setParameter(i, values[i]);
				}
				return query.iterate();
			}

		});
	}

	public DetachedCriteria createDetachedCriteria(Class clz) {
		return DetachedCriteria.forClass(clz);
	}

	public Criteria createCriteria(final Class<?> clz) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<Criteria>() {
			public Criteria doInHibernate(Session session) throws HibernateException {
				return createDetachedCriteria(clz).getExecutableCriteria(session);
			}
		});
	}

	public List findByCriteria(final DetachedCriteria criteria) {
		return getTemplate().execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				return criteria.getExecutableCriteria(session).list();
			}
		});
	}

	public <T> List<T> findByCriteria(final DetachedCriteria criteria, final int firstResult, final int maxResults) {
		return getTemplate().execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				return criteria.getExecutableCriteria(session).setFirstResult(firstResult).setMaxResults(maxResults)
						.list();
			}
		});
	}

	public <T> List<T> findByCriteria(final Class<?> clz, final Criterion... criterion) {
		return getTemplate().executeWithNativeSession(new HibernateCallback<List>() {
			public List doInHibernate(Session session) throws HibernateException {
				Criteria crit = session.createCriteria(clz);
				for (Criterion c : criterion) {
					if (c != null) {
						crit.add(c);
					}
				}
				List list = crit.list();
				return list;
			}

		});
	}

	public Integer getRowCount(DetachedCriteria criteria) {
		criteria.setProjection(Projections.rowCount());
		List list = this.findByCriteria(criteria, 0, 1);
		return (Integer) list.get(0);

	}

	public Object getStatValue(DetachedCriteria criteria, String propertyName, String StatName) {
		if (StatName.toLowerCase().equals("max"))
			criteria.setProjection(Projections.max(propertyName));
		else if (StatName.toLowerCase().equals("min"))
			criteria.setProjection(Projections.min(propertyName));
		else if (StatName.toLowerCase().equals("avg"))
			criteria.setProjection(Projections.avg(propertyName));
		else if (StatName.toLowerCase().equals("sum"))
			criteria.setProjection(Projections.sum(propertyName));
		else
			return null;
		List list = this.findByCriteria(criteria, 0, 1);
		return list.get(0);
	}

	public int update(final String hql, final Object... values) {
		return (Integer) getTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session arg0) throws HibernateException {
				Query query = arg0.createQuery(hql);
				for (int i = 0; i < values.length; i++) {
					query.setParameter(i, values[i]);
				}
				return query.executeUpdate();
			}
		});

	};

	@Override
	public <T> void execNativeSQL(final List<String> sqls, final List<List<Object>> params) throws Exception {
		if (sqls == null || sqls.size() == 0) {
			throw new Exception("无效的SQL语句。");
		}
		if (params != null && params.size() > 0 && sqls.size() != params.size()) {
			throw new Exception("params size 和SQLS 的长度不一致!");
		}
		try {
			getTemplate().execute(new HibernateCallback<T>() {
				@Override
				public T doInHibernate(Session session) throws HibernateException {
					session.doWork(new Work() {
						public void execute(Connection connection) throws SQLException {
							String DB_OEM = connection.getMetaData().getDatabaseProductName();
							connection.setAutoCommit(false);
							PreparedStatement pstmt = null;
							String sql = "";
							List<Object> cparams = null;
							for (int i = 0; i < sqls.size(); i++) {
								sql = sqls.get(i);
								pstmt = connection.prepareStatement(sql);
								if (params != null) {
									cparams = params.get(i);
									if (cparams != null) {
										for (int k = 0; k < cparams.size(); k++) {
											Object value = cparams.get(k);
											// derby null val bug.
											if (value == null && DB_OEM.equals("Apache Derby")) {
												pstmt.setNull(k + 1, Types.VARCHAR);
											} else {
												pstmt.setObject(k + 1, value);
											}

										}
									}
								}
								pstmt.execute();
								pstmt.close();
							}
							connection.commit();

						}
					});
					return null;
				}
			});
		} catch (Exception e) {
			throw e;
		}

	}

	@Override
	public <T> void execNativeSQL(String sql, List<Object> params) throws Exception {
		List<String> sqls = new ArrayList<String>();
		sqls.add(sql);
		List<List<Object>> gparams = new ArrayList<List<Object>>();
		gparams.add(params);
		execNativeSQL(sqls, gparams);
	}

	@Override
	public <T> void execNativeSQL(String sql) throws Exception {
		List<String> sqls = new ArrayList<String>();
		sqls.add(sql);
		execNativeSQL(sqls, null);
	};

	@Override
	public <T> void execAllNativeSQL(List<String> sql) throws Exception {
		List<String> sqls = new ArrayList<String>();
		sqls.addAll(sql);
		execNativeSQL(sqls, null);
	};

	@Override
	public <T> List<Map<String, Object>> execNativeQuerySQL(String sql) throws Exception {
		try {
			T t = getTemplate().execute(new HibernateCallback<T>() {
				@Override
				public T doInHibernate(Session session) throws HibernateException {
					List<Map<String, Object>> list = new ArrayList<>();
					session.doWork(new Work() {
						public void execute(Connection connection) throws SQLException {
							String DB_OEM = connection.getMetaData().getDatabaseProductName();
							connection.setAutoCommit(false);
							PreparedStatement pstmt = null;
							pstmt = connection.prepareStatement(sql);
							ResultSet resultSet = pstmt.executeQuery();
							ResultSetMetaData rsmd1 = resultSet.getMetaData();
							int count = rsmd1.getColumnCount();
							while (resultSet.next()) {
								HashMap<String, Object> map = new HashMap<String, Object>();
								for (int i = 1; i <= count; i++) {
									String columnName = rsmd1.getColumnLabel(i);
									Object value = resultSet.getObject(i);
									map.put(columnName, value);
								}
								list.add(map);
							}
							pstmt.execute();
							pstmt.close();
							connection.commit();

						}
					});
					return (T) list;
				}
			});
			return (List<Map<String, Object>>) t;
		} catch (Exception e) {
			throw e;
		}

	}

	public void flush() {
		template.flush();
	}

}
